package com.igd.utils.dev;

import java.io.File;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.Element;
import org.dom4j.io.SAXReader;

import com.igd.common.constant.IConstant;
import com.igd.exceptions.IgdException;
import com.igd.pojo.vo.dev.TableFieldData;
import com.igd.utils.CommonUtil;
import com.igd.utils.Dom4jUtil;
import com.igd.utils.dev.dbmapper.impl.DbMapper;

 /**
 * @version  1.0
 *
 * @author   QIAOYU
 *
 * @date     2013-7-8
 *
 * @description	 数据库对象反转成Java对象与ibatis sqlmap文件
 */

public class DbMapperUtil {
	
	private Logger logger = Logger.getLogger(this.getClass());
	
	private Dom4jUtil domUtil = Dom4jUtil.getInstance();
	
	/**私有作用域*/
	private final String PRIVATE_SCOPE  = "private";
	
	/**公有作用域*/
	private final String PUBLIC_SCOPE = "public";
	
	/**JavaBean类型*/
	private final String CLASS = "class";
	
	/**Java代码结束符*/
	private final String CODE_END = ";";
	
	/**字段解析分隔符*/
	private String FIELD_SPLIT_FLAG = "_";
	
	/**Oracle数据库产品名*/
	private final String ORACLE_DATABASE = "oracle";
	
	private static DbMapperUtil dbMapperUtil = null;
	
	private DbMapperUtil(){};
	
	public static DbMapperUtil getInstance(){
		
		if(null == dbMapperUtil){
			
			synchronized (DbMapperUtil.class) {
				
				dbMapperUtil = new DbMapperUtil();

			}
		}
		
		return dbMapperUtil;
	}
	
	/**
	 * 获取数据库连接
	 * 
	 * @param driverName
	 * 
	 * @param url
	 * 
	 * @param userName
	 * 
	 * @param passWord
	 * 
	 * @return Connection
	 * 
	 * @throws ClassNotFoundException
	 * 
	 * @throws SQLException
	 * 
	 * @throws IgdException 
	 */
	public Connection getDbConnection(String driverName,String url,String userName,String passWord) throws ClassNotFoundException, SQLException, IgdException{
		
		Connection conn = null;
		
		if(StringUtils.isNotEmpty(driverName) && StringUtils.isNotEmpty(url) && StringUtils.isNotEmpty(userName) && StringUtils.isNotEmpty(passWord)){
			
			Class.forName(driverName);
			
			conn = DriverManager.getConnection(url, userName, passWord);
			
			logger.info("get db connection info is " + conn);
			
			if(null != conn){
				
				return conn;

			}else{
				
				throw new IgdException("dbmapper.getDbConnectionError");
				
			}
		}
		
		return null;
	}
	
	/**
	 * 获取数据库所有表名
	 * 
	 * @param conn
	 * 
	 * @param schema
	 * 
	 * @throws SQLException
	 * 
	 * @return List<String>
	 */
	public List<String> getTables(Connection conn,String schema) throws SQLException{
		
		List<String> tableNames = null;
		
		if(null != conn){
			
			DatabaseMetaData dmd = conn.getMetaData();
			
			ResultSet rs = null;
			
			if(this.isOracle(conn)){
				
				rs = dmd.getTables(null,schema.toUpperCase(),"%",new String[]{"TABLE"});
				
			}else{
				
				rs = dmd.getTables(schema.toUpperCase(),"%","%",new String[]{"TABLE"});
				
			}
			
			if(null != rs){
				
				tableNames = new ArrayList<String>();
				
				while(rs.next()){
					
					String tableName = rs.getString("TABLE_NAME");

					if(StringUtils.isNotEmpty(tableName)){
						
						tableNames.add(tableName.toUpperCase());

					}
				}
				
				return tableNames;
			}
		}
		
		return null;
	}
	
	/**
	 * 根据数据库表名获取表字段信息
	 * 
	 * @param conn
	 * 
	 * @param tableName
	 * 
	 * @param schema
	 * 
	 * @return List<TableFieldData>
	 * 
	 * @throws SQLException
	 */
	private List<TableFieldData> getTableFields(Connection conn,String tableName,String schema) throws SQLException{
		
		if(null != conn){
			
			DatabaseMetaData dmd = conn.getMetaData();
			
			ResultSet rs = null;
			
			if(this.isOracle(conn)){
				
				rs = dmd.getColumns(null,schema.toUpperCase(),tableName,"%");
				
			}else{
				
				rs = dmd.getColumns(schema.toUpperCase(),"%",tableName,"%");

			}
			
			
			if(null != rs){
				
				List<TableFieldData> fieldList = new ArrayList<TableFieldData>();
				
				while(rs.next()){
					
					String columnName = rs.getString("COLUMN_NAME");
					
					String columnType = rs.getString("TYPE_NAME");
					
					String columnDesc = rs.getString("REMARKS");
					
					int columnTypeInfo = rs.getInt("DATA_TYPE");
					
					TableFieldData fieldData = new TableFieldData(columnName,columnType,columnDesc,columnTypeInfo);
					
					fieldList.add(fieldData);

				}
				
				return fieldList;
			}
		}
		
		return null;
	}
	
	/**
	 * 创建数据库字段信息转换成Java代码
	 * 
	 * @param fieldData 数据库字段信息
	 * 
	 * @return
	 */
	private String createJavaFiledCode(TableFieldData fieldData){
		
		if(null != fieldData){
			
			DbMapper mapper = new DbMapper();
			
			String fieldName = this.createFieldName(fieldData.getColumnName());
			
			String fieldType = mapper.getDbMapper().get(fieldData.getColumnTypeInfo());
			
			String remark = fieldData.getColumnDesc();
			
			String fieldStr = this.PRIVATE_SCOPE.concat(IConstant.BLANK_STRING.concat(fieldType).concat(IConstant.BLANK_STRING).concat(fieldName).concat(this.CODE_END));
			
			if(StringUtils.isNotEmpty(remark)){
				
				String fieldRemark = IConstant.LEFT_NOTE.concat(remark).concat(IConstant.RIGHT_NOTE).concat(IConstant.HTML_LINE).concat(fieldStr);

				return fieldRemark;
				
			}else{
				
				return fieldStr;
			}
		}
		
		return null;
	}
	
	/**
	 * 根据表名创建JavaBean代码
	 * 
	 * @param conn 数据库连接
	 * 
	 * @param tableName 数据库表名
	 * 
	 * @return String
	 * 
	 * @throws SQLException
	 */
	public String mapperJavaCode(Connection conn,String tableName,String schema) throws SQLException{
		
		List<TableFieldData> fieldDateList = this.getTableFields(conn, tableName, schema);
		
		if(null != fieldDateList && fieldDateList.size()>0){
			
			StringBuffer buffer = new StringBuffer();
			
			buffer.append(this.PUBLIC_SCOPE.concat(IConstant.BLANK_STRING).concat(this.CLASS).concat(IConstant.BLANK_STRING).concat(this.createClassName(tableName)).concat(IConstant.LEFT_BRACKETS).concat(IConstant.HTML_LINE));
			
			for(TableFieldData tfd:fieldDateList){
				
				String javaCode = this.createJavaFiledCode(tfd);

				buffer.append(javaCode.concat(IConstant.HTML_LINE));

			}
			
			buffer.append(IConstant.RIGHT_BRACKETS.concat(IConstant.HTML_LINE));
			
			return buffer.toString();
		}
		
		return null;
	}
	
	/**
	 * 根据表名创建增删改查ibatis sqlmap代码
	 * 
	 * @param conn
	 * 
	 * @param tableName
	 * 
	 * @return String
	 * 
	 * @throws SQLException
	 * 
	 * @throws DocumentException
	 */
	public String mapperIbatisCode(Connection conn,String tableName,String schema) throws SQLException, DocumentException{
		
		List<TableFieldData> fieldDateList = this.getTableFields(conn, tableName, schema);
		
		if(null != fieldDateList && fieldDateList.size() > 0){
			
			StringBuffer buffer = new StringBuffer();
			
			String insertSql = this.createIbatisInsertSql(tableName, fieldDateList);
			
			String updateSql = this.createIbatisUpdateSql(tableName, fieldDateList);
			
			String selectSql = this.createIbatisSelectSql(tableName, fieldDateList);
			
			buffer.append(insertSql).append(IConstant.HTML_LINE).append(IConstant.HTML_LINE);
			
			buffer.append(updateSql).append(IConstant.HTML_LINE).append(IConstant.HTML_LINE);
			
			buffer.append(selectSql).append(IConstant.HTML_LINE).append(IConstant.HTML_LINE);
			
			return buffer.toString();
		}
		
		return null;
	}
	
	/**
	 * 创建Ibatis文件中的字段信息,针对插入操作
	 * 
	 * @param fieldData
	 * 
	 * @return
	 * 
	 * @throws DocumentException 
	 */
	private String createInsertIbatisFieldCode(TableFieldData fieldData) throws DocumentException{
		
		if(null != fieldData){
			
			String beanField = this.createFieldName(fieldData.getColumnName());
			
			String dbField = fieldData.getColumnName().toUpperCase();
			
			Element fieldTemplate = (Element) this.initSqlTemplate().get("field").elements().get(0);
			
			if(null != fieldTemplate){
				
				String templateStr = fieldTemplate.getTextTrim();
				
				return templateStr.replace("beanField",beanField).replace("dbField",dbField);

			}
		}
		
		return null;
	}
	
	/**
	 * 创建Ibatis文件中的赋值字段信息,针对插入操作
	 * 
	 * @param fieldData
	 * 
	 * @return
	 * 
	 * @throws DocumentException 
	 */
	private String createInsertIbatisValueCode(TableFieldData fieldData) throws DocumentException{
		
		if(null != fieldData){
			
			String beanField = this.createFieldName(fieldData.getColumnName());
			
			Element fieldTemplate = (Element) this.initSqlTemplate().get("value").elements().get(0);
			
			if(null != fieldTemplate){
				
				String templateStr = fieldTemplate.getTextTrim();
				
				return templateStr.replaceAll("beanFiled",beanField);

			}
		}
		
		return null;
	}
	
	/**
	 * 创建ibatis文件中update的字段信息,针对修改操作
	 * 
	 * @param fieldData
	 * 
	 * @return String
	 * 
	 * @throws DocumentException
	 */
	private String createUpdIbatisValueCode(TableFieldData fieldData) throws DocumentException{
		
		if(null != fieldData){
			
			String beanField = this.createFieldName(fieldData.getColumnName());
			
			String dbField = fieldData.getColumnName().toUpperCase();
			
			Element fieldTemplate = (Element) this.initSqlTemplate().get("updateField").elements().get(0);
			
			if(null != fieldTemplate){
				
				String templateStr = fieldTemplate.getTextTrim();
				
				return templateStr.replace("dbField",dbField).replaceAll("beanField",beanField);

			}
		}
		
		return null;
	}
	
	/**
	 * 创建ibatis文件中select字段信息,针对查询操作
	 * 
	 * @param tableName
	 * 
	 * @param fieldData
	 * 
	 * @return
	 * 
	 * @throws DocumentException 
	 */
	private String createSelectIbatisFieldCode(String tableName,TableFieldData fieldData) throws DocumentException{
		
		if(StringUtils.isNotEmpty(tableName) && null != fieldData){
			
			String nickeName = this.createClassName(tableName);
			
			String beanField = this.createFieldName(fieldData.getColumnName());
			
			String dbField = fieldData.getColumnName().toUpperCase();
			
			Element fieldTemplate = (Element) this.initSqlTemplate().get("selectField").elements().get(0);
			
			if(null != fieldTemplate){
				
				String templateStr = fieldTemplate.getTextTrim();
				
				return templateStr.replace("#nickName#",nickeName).replace("#dbField#",dbField).replace("#beanField#",beanField);

			}
		}
		
		return null;
	}
	
	/**
	 * 创建ibatis文件中select查询条件字段信息,针对查询操作
	 * 
	 * @param tableName
	 * 
	 * @param fieldData
	 * 
	 * @return String
	 * 
	 * @throws DocumentException
	 */
	private String createSelectIbatisConditionFieldCode(String tableName,TableFieldData fieldData) throws DocumentException{
		
		if(StringUtils.isNotEmpty(tableName) && null != fieldData){
			
			String nickeName = this.createClassName(tableName);
			
			String beanField = this.createFieldName(fieldData.getColumnName());
			
			String dbField = fieldData.getColumnName().toUpperCase();
			
			Element fieldTemplate = (Element) this.initSqlTemplate().get("selectConditionField").elements().get(0);
			
			if(null != fieldTemplate){
				
				String templateStr = fieldTemplate.getTextTrim();
				
				return templateStr.replace("beanField",beanField).replace("#nickName#",nickeName).replace("#dbField#",dbField);

			}
		}
		
		return null;
	}
	
	/**
	 * 创建ibatis数据插入语句
	 * 
	 * @param tableName
	 * 
	 * @param fieldDateList
	 * 
	 * @return String
	 * 
	 * @throws DocumentException
	 */
	private String createIbatisInsertSql(String tableName,List<TableFieldData> fieldDateList) throws DocumentException{
		
		if(StringUtils.isNotEmpty(tableName) && null != fieldDateList && fieldDateList.size() > 0){
			
			Element insertSql = this.initSqlTemplate().get("insert");
			
			String insertBegin =  insertSql.element("insertBegin").getTextTrim();
			
			String insertEnd = insertSql.element("insertEnd").getTextTrim();
			
			String valueBegin = insertSql.element("valueBegin").getTextTrim();
			
			String valueEnd = insertSql.element("valueEnd").getTextTrim();
			
			if(StringUtils.isNotEmpty(insertBegin) && StringUtils.isNotEmpty(insertEnd) && StringUtils.isNotEmpty(valueBegin) && StringUtils.isNotEmpty(valueEnd)){
				
				insertBegin = insertBegin.replace("#tableName#",tableName.toUpperCase());
				
				StringBuffer begin = new StringBuffer();
				
				StringBuffer end = new StringBuffer();
				
				begin.append(insertBegin.concat(IConstant.HTML_LINE));
				
				end.append(valueBegin.concat(IConstant.HTML_LINE));
				
				for(TableFieldData tfd : fieldDateList){
					
					String fieldCode = this.createInsertIbatisFieldCode(tfd);

					String valueCode = this.createInsertIbatisValueCode(tfd);
					
					begin.append(fieldCode.concat(IConstant.HTML_LINE));
					
					end.append(valueCode.concat(IConstant.HTML_LINE));

				}
				
				begin.append(insertEnd);
				
				end.append(valueEnd);
				
				return begin.append(IConstant.HTML_LINE).append(end).toString();
			}
		}
		
		return null;
	}
	
	/**
	 * 创建ibatis数据修改语句
	 * 
	 * @param tableName
	 * 
	 * @param fieldDateList
	 * 
	 * @return
	 * 
	 * @throws DocumentException 
	 */
	private String createIbatisUpdateSql(String tableName,List<TableFieldData> fieldDateList) throws DocumentException{
		
		if(StringUtils.isNotEmpty(tableName) && null != fieldDateList && fieldDateList.size() > 0){
			
			Element updSql = this.initSqlTemplate().get("update");
			
			String updateBegin = updSql.element("updateBegin").getTextTrim();
			
			String updateEnd = updSql.element("updateEnd").getTextTrim();
			
			if(StringUtils.isNotEmpty(updateBegin) && StringUtils.isNotEmpty(updateEnd)){
				
				updateBegin = updateBegin.replace("#tableName#",tableName.toUpperCase());
				
				StringBuffer buffer = new StringBuffer();
				
				buffer.append(updateBegin.concat(IConstant.HTML_LINE));
				
				for(TableFieldData tfd : fieldDateList){
					
					String fieldCode = this.createUpdIbatisValueCode(tfd);
					
					buffer.append(fieldCode.concat(IConstant.HTML_LINE));

				}
				
				buffer.append(updateEnd).append(IConstant.HTML_LINE);
				
				return buffer.toString();
			}
		}
		
		return null;
	}
	
	/**
	 * 创建Ibatis数据查询语句
	 * 
	 * @param tableName
	 * 
	 * @param fieldDateList
	 * 
	 * @return
	 * 
	 * @throws DocumentException
	 */
	private String createIbatisSelectSql(String tableName,List<TableFieldData> fieldDateList) throws DocumentException{
		
		if(StringUtils.isNotEmpty(tableName) && null != fieldDateList && fieldDateList.size() > 0){
			
			Element updSql = this.initSqlTemplate().get("select");
			
			String selectBegin = updSql.element("selectBegin").getTextTrim();
			
			String selectEnd = updSql.element("selectEnd").getTextTrim();
			
			String conditionEnd = updSql.element("conditionEnd").getTextTrim();
			
			String conditionBegin = updSql.element("conditionBegin").getTextTrim();
			
			if(StringUtils.isNotEmpty(selectBegin) && StringUtils.isNotEmpty(selectEnd)){
				
				selectEnd = selectEnd.replace("#tableName#",tableName.toUpperCase()).replace("#nickName#",this.createClassName(tableName));
				
				StringBuffer filedBuffer = new StringBuffer();
				
				StringBuffer conditionBuffer = new StringBuffer();
				
				filedBuffer.append(selectBegin.concat(IConstant.HTML_LINE));
				
				conditionBuffer.append(conditionBegin.concat(IConstant.HTML_LINE));
				
				for(TableFieldData tfd : fieldDateList){
					
					String fieldCode = this.createSelectIbatisFieldCode(tableName,tfd);
					
					String conditionCode = this.createSelectIbatisConditionFieldCode(tableName, tfd);
					
					filedBuffer.append(fieldCode.concat(IConstant.HTML_LINE));
					
					conditionBuffer.append(conditionCode.concat(IConstant.HTML_LINE));

				}
				
				filedBuffer.append(selectEnd).append(IConstant.HTML_LINE);
				
				conditionBuffer.append(conditionEnd).append(IConstant.HTML_LINE);
				
				return filedBuffer.append(conditionBuffer).toString();
			}
		}
		
		return null;
	}
	
	/**
	 * 创建JavaBean字段名称,根据下划线规则处理,如无下划线则原样返回,如有下划线则返回驼峰格式代码例如:user_create_time则返回userCreateTime
	 * 
	 * @param columnName
	 * 
	 * @return String
	 */
	private String createFieldName(String columnName){
		
		if(StringUtils.isNotEmpty(columnName)){
			
			String lowerName = columnName.toLowerCase();
			
			String [] str = lowerName.split(this.getFIELD_SPLIT_FLAG());
			
			if(null == str){
				
				return lowerName;
				
			}else{
				
				StringBuffer buffer = new StringBuffer();
				
				for(int i=0;i<str.length;i++){
					
					if(i == 0){
						
						buffer.append(str[i]);

					}else{
						
						String s = str[i].substring(0,1).toUpperCase().concat(str[i].substring(1,str[i].length()));
						
						buffer.append(s);

					}
				}
				
				return buffer.toString();
			}
		}
		
		return null;
	}
	
	/**
	 * 创建JavaBean名称,根据下划线规则处理,如无下划线则原样返回,如有下划线则返回正常Java类名称,如tbl_mer_merchant则返回TblMerMerchant
	 * 
	 * @param tableName
	 * 
	 * @return String
	 */
	private String createClassName(String tableName){
		
		if(StringUtils.isNotEmpty(tableName)){
			
			String lowerName = tableName.toLowerCase();
			
			String [] str = lowerName.split(this.getFIELD_SPLIT_FLAG());
			
			if (null == str) {

				return lowerName.substring(0, 1).toUpperCase().concat(lowerName.substring(1, lowerName.length()).toLowerCase());

			} else {

				StringBuffer buffer = new StringBuffer();

				for (int i = 0; i < str.length; i++) {

					buffer.append(str[i].substring(0, 1).toUpperCase().concat(str[i].substring(1, str[i].length())));

				}

				return buffer.toString();
			}
		}

		return null;
	}
	
	/**
	 * 初始化SQL模板信息
	 * 
	 * @return Map<String,Element>
	 * 
	 * @throws DocumentException
	 */
	private Map<String,Element> initSqlTemplate() throws DocumentException{

		String templatePath = this.getTemplateFilePath();
		
		if(StringUtils.isNotEmpty(templatePath)){
			
			SAXReader reader = new SAXReader();
			
			Document doc = reader.read(new File(templatePath));
			
			if(null != doc){
				
				domUtil.setDoc(doc);
				
				Element root = domUtil.getRoot();
				
				@SuppressWarnings("unchecked")
				List<Element> list = root.elements();
				
				if(null != list && list.size()>0){
					
					HashMap<String,Element> map = new HashMap<String,Element>(list.size());
					
					for(Element e:list){
						
						String pageId = e.attribute("id").getValue();
						
						map.put(pageId, e);

					}
					
					return map;
				}
			}
		}
		
		return null;
	}
	
	/**
	 * 获取数据库产品名称,Oracle返回Oracle,Mysql返回MySQL,返回的产品名称转小写统一处理
	 * 
	 * @param conn
	 * 
	 * @return
	 * 
	 * @throws SQLException
	 */
	private String getDataBaseProduct(Connection conn) throws SQLException{

		return null != conn ? conn.getMetaData().getDatabaseProductName().toLowerCase() : null;

	}
	
	/**
	 * 判断是否是Oracle数据库
	 * 
	 * @param conn
	 * 
	 * @return
	 * 
	 * @throws SQLException
	 */
	private boolean isOracle(Connection conn) throws SQLException{
		
		return null != conn ? this.getDataBaseProduct(conn).equals(this.ORACLE_DATABASE) : null;

	}
	
	/**
	 * 获取SQL模板文件路径
	 * 
	 * @return
	 */
	private String getTemplateFilePath(){
		
		String fileName = "igd.sqltemplate.xml";
		
		return CommonUtil.getClassPathFile(fileName);

	}
	
	public String getFIELD_SPLIT_FLAG() {
		return FIELD_SPLIT_FLAG;
	}

	public void setFIELD_SPLIT_FLAG(String fIELD_SPLIT_FLAG) {
		FIELD_SPLIT_FLAG = fIELD_SPLIT_FLAG;
	}
}
